package com.cmnit.analysis.dao

import com.cmnit.analysis.common.{TDao, TSql}
import org.apache.log4j.Logger
import org.apache.spark.sql.DataFrame

class NoTransWithPicDao extends TSql with TDao {
  private val logger: Logger = Logger.getLogger(this.getClass)

  /**
   * 加工结果数据
   */
  def NoTransWithPicData(sqlTime: String, acctTime: String): DataFrame = {
    sparkSQL(
      "SELECT " +
        "NVL(roadId,' ') roadId, " +
        "NVL(roadName,' ') roadName, " +
        "NVL(gantryId,' ') gantryId, " +
        "NVL(name,' ') gantryName, " +
        "NVL(vehicleType,' ') vehicleType, " +
        "NVL(SPLIT(vehiclePlate,'_')[0],' ') vehiclePlate, " +
        "NVL(vehicleColor,' ') vehicleColor, " +
        "statisday, " +
        "NVL(transTime,' ') transTime, " +
        "NVL(picTime,' ') picTime, " +
        "NVL(gantryType,' ') gantryType, " +
        "NVL(picId,' ') picId, " +
        "CEIL(ROW_NUMBER() OVER(PARTITION BY statisday ORDER BY '') / 2000) reqbatchno, " +
        "CEIL(COUNT(statisday) OVER() / 2000) maxbatchno " +
        "FROM " +
        "(SELECT " +
        "t.*, " +
        "'" + acctTime + "' as statisday " +
        "FROM " +
        "(SELECT " +
        "a.roadId, " +
        "a.roadName, " +
        "a.gantryId, " +
        "a.vehiclePlate, " +
        "a.vehicleColor, " +
        "a.picTime, " +
        "a.picId, " +
        "b.vehicleType, " +
        "b.transTime, " +
        "b.gantryType " +
        "FROM " +
        "(SELECT " +
        "DISTINCT " +
        "roadId, " +
        "roadName, " +
        "gantryId, " +
        "vehiclePlate, " +
        "vehicleColor, " +
        "picTime, " +
        "picId " +
        "FROM " +
        "dwd.dwd_etc_gantryvehdisdata_filter " +
        "WHERE "
        + sqlTime +
        "AND SUBSTR(vehiclePlate, 1, 1) != '默' " +
        "AND gantryId IN ('G007036019000520010','G007036019000220010','G007036019000510010','G007036019000210010')) a " +
        "LEFT JOIN " +
        "(SELECT " +
        "DISTINCT " +
        "vehiclePlate, " +
        "vehicleType, " +
        "transTime, " +
        "gantryId, " +
        "gantryType " +
        "FROM " +
        "dwd.dwd_etc_gantryetcbill_filter " +
        "WHERE "
        + sqlTime +
        "AND ((mediatype = '1' AND obutraderesult = '0') OR (mediatype = '2' AND traderesult = '0')) " +
        "AND SUBSTR(vehiclePlate, 1, 1) != '默' " +
        "AND gantryId IN ('G007036019000520010','G007036019000220010','G007036019000510010','G007036019000210010')) b " +
        "ON a.vehiclePlate = b.vehiclePlate AND a.gantryId = b.gantryId) t " +
        "WHERE " +
        "(ABS((UNIX_TIMESTAMP(REPLACE(t.picTime, 'T', ' ')) - UNIX_TIMESTAMP(REPLACE(t.transTime, 'T', ' '))) / 60) > 5 " +
        "AND ABS((UNIX_TIMESTAMP(REPLACE(t.picTime, 'T', ' ')) - UNIX_TIMESTAMP(REPLACE(t.transTime, 'T', ' '))) / 60) < 10) " +
        "OR (transTime IS NULL)) c " +
        "LEFT JOIN " +
        "(SELECT  " +
        "id, " +
        "name " +
        "FROM  " +
        "dim.dim_roadbaseinfo_jiujiang " +
        "WHERE  " +
        "lastver IN (SELECT MAX(lastver) FROM dim.dim_roadbaseinfo_jiujiang)) d " +
        "ON c.gantryId = d.id")
  }
}
